package com.huidao.dao.buyer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.huidao.entity.Goods;
import com.huidao.entity.GoodsType;
import com.huidao.entity.Shoppingcart;
import com.huidao.entity.User;
import com.huidao.util.JDBCUtil;



public class ShoppingCartDaoImpl implements ShoppingCartDao {
	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	@Override
	public boolean addShoppingcart(Shoppingcart shoppingcart) {
		String sql = "INSERT INTO SHOPPINGCART (goods_id,GOODS_NUM,USER_ID)VALUES(?,?,?)";
		try {
			conn = JDBCUtil.getConn();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			ps.setLong(1, shoppingcart.getGood().getGid());
			ps.setInt(2, shoppingcart.getGoods_num());
			ps.setLong(3, shoppingcart.getUser().getUserId());
			int i = ps.executeUpdate();
			if (i>0) {
				String sql1 = "UPDATE GOODS SET GOODS_ACCOUNT=? WHERE GOODS_ID=?";
				ps = conn.prepareStatement(sql1);
				ps.setLong(1, shoppingcart.getGood().getgAccount()-shoppingcart.getGoods_num());
				ps.setLong(2, shoppingcart.getGood().getGid());
				int j = ps.executeUpdate();
				if (j>0) {
					conn.commit();
				return true;
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			JDBCUtil.free(conn, ps, rs);
		}
		return false;
	}

	@Override
	public boolean updateGoods_num(long shoppingcart_id,int goods_num){
		String sql = "UPDATE `SHOPPINGCART` SET goods_num=? WHERE shoppingcart_id=?";
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, goods_num);
			ps.setLong(2, shoppingcart_id);
			return ps.executeUpdate() > 0;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.free(conn, ps, rs);
		}
		return false;
		
	}


	@Override
	public boolean delShoppingcart(long Shoppingcart_id) {
		String sql = "DELETE FROM `SHOPPINGCART` WHERE SHOPPINGCART_ID=?";
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setLong(1, Shoppingcart_id);
			int i = ps.executeUpdate();
			if (i > 0) {
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.free(conn, ps, rs);
		}

		return false;
	}

	@Override
	public List<Shoppingcart> getShoppingcartList(int currentpage,int pagesize, String keyName,long id) {
		List<Shoppingcart> list = new ArrayList<Shoppingcart>();
		String sql = "SELECT * FROM SHOPPINGCART A LEFT JOIN GOODS B ON A.GOODS_ID=B.GOODS_ID LEFT JOIN USER C ON A.USER_ID = C.USER_ID LEFT JOIN GOODSTYPE E ON B.GOODSTYPE_ID = E.GOODSTYPE_ID WHERE 1=1";
		if (keyName != null && !keyName.trim().equals("")) {
			sql = sql + "  AND b.goods_name LIKE '%" + keyName + "%'";
		}
		if (id != 0) {
			sql = sql + " AND shoppingcart_id = ' " + id + " ' ";
		}
		sql = sql + " LIMIT ?,?";
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, (currentpage - 1) * pagesize);
			ps.setInt(2, pagesize);
			rs = ps.executeQuery();
			while (rs.next()) {
				Shoppingcart shoppingcart = new Shoppingcart();
				Goods goods = new Goods();
				User user1 = new User();
				GoodsType gp = new GoodsType();
				shoppingcart.setShoppingcart_id(rs.getLong("shoppingcart_id"));
				shoppingcart.setGoods_num(rs.getInt("goods_num"));
				gp.setGoodstype_name(rs.getString("goodstype_name"));
				goods.setGoodsType(gp);
				goods.setGid(rs.getLong("goods_id"));
				goods.setgName(rs.getString("goods_name"));
				goods.setgPrice(rs.getDouble("goods_price"));
				goods.setgAccount(rs.getInt("goods_account"));
				user1.setUserId(rs.getLong("user_id"));
				user1.setUserName(rs.getString("user_name"));
				user1.setUserShopName(rs.getString("user_shopname"));
				user1.setUserPhone(rs.getString("user_phone"));
				shoppingcart.setGood(goods);
				shoppingcart.setUser(user1);
				list.add(shoppingcart);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.free(conn, ps, rs);
		}

		return list;
	}

	public boolean clearShoppingcart(User user) {
		String sql = "DELETE FROM `SHOPPINGCART` WHERE USER_ID=?";
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql,1);
			ps.setLong(1, user.getUserId());
			int i = ps.executeUpdate();
			if (i > 0) {
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.free(conn, ps, rs);
		}

		return false;
	}

	public int getTotalPage(String keyName,long id) {
		String sql = "SELECT COUNT(*) FROM SHOPPINGCART A LEFT JOIN GOODS B ON A.GOODS_ID=B.GOODS_ID WHERE 1=1";
		if (keyName != null && !keyName.trim().equals("")) {
			sql = sql + "  AND goods_name LIKE '%"+ keyName +"%'";
		}
		
		if (id != 0) {
			sql = sql + "  AND shoppingcart_id = ' " + id + " ' ";
		}
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.free(conn, ps, rs);
		}
		return 0;
	}



}
